
*proc printto log= "./logs/SAS_05_pull_formnl.log" new;
*run;

/*************************************************
Program: pull_formnl.sas
Author: hannah rubinton
Date: 12/19/2018

Summary: output a stata dataset with information on affiliates of foreign multinationals in the U.S. 

inputs: 
-2012 foreign MNL form data set (gvc2007_be15_form.sas7bdat)
-2012 foreign MNL entity data set (gvc2007_be15_entity.sas7bdat)
-2012 crosswalk between us_id and firmid  (be12_crosswalk_2012.sas7bdat)

output: 
1. affilliate level dataset, keys=firmid X foreign_id  
  covariates: emp, sales, assets, industry, country. 
  

Notes: 

**************************************************/

options obs=max;
    
*Raw census files ;
 %include 'yyyy/pdata.sas' ;


libname hannahcw 'xxxx/input/Hannah/crosswalk' ;
libname hannahbe 'xxxx/input/Hannah/be15' ;
libname transfer 'xxxx/input/transfer20160318/' ;

libname input "xxxx/input/";   /*  symbolic links to raw census data are in input folder */

*debugging;
libname input 'xxxx/input/' ;
libname output "xxxx/data/";

%let output=xxxx/data ;


libname tempdata  'xxxx/data/junk/';


%macro pull_formnl(year);

/******************************
*check for duplicates before merging files together;
********************************/
proc freq data=hannahcw.affiliate_crosswalk_&year;
  tables usid /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=hannahbe.gvc&year._be15_form;
  tables usid /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=hannahbe.gvc&year._be15_entity;
  tables usid /noprint out=keylist;
run;
proc print; where count ge 2; run;


*merge in firmids;
proc sql; 
create table formnl as 
select A.*, B.firmid, C.name as bea_name,
indaff, indparent, TypeOwnership, ein1, ein2, match_flag, state, street1, CtryParent, CtryUbo
from hannahbe.gvc&year._be15_form as A 
left join hannahcw.affiliate_crosswalk_&year. as B on A.usid eq B.usid
left join hannahbe.gvc&year._be15_entity as C on A.usid eq C.usid;


proc freq data=formnl;
  tables usid /noprint out=keylist;
run;
proc print; where count ge 2; run;

/******************************
*. create forein affiliate level bea dataset;
********************************/


*select variables and create some new indicators of type of parent;
proc sql;
create table output.formnl_us_affiliates&year as 
select usid, firmid, bea_name, 
  CtryParent as in_ctryparent, CtryUbo as in_ctryubo, 
  typeownership as in_typeownership,
  indaff as in_indaff, 
  indparent as in_indparent,
  ein1 as in_ein1, ein2 as in_ein2, 
  match_flag as in_match_flag,
  state as in_state, street1 as in_street1,
  employment as in_emp,
  employeecompensation as in_empcomp,
  exports as in_exports,
  exportstoforeignaffiliates as in_exp_toforaff,
  exportstoforeignparentgroup as in_exp_tofpg,
  exportstounaffiliatedforeigners as in_exp_toothfor,
  imports as in_imports,
  importsfromforeignaffiliates as in_imp_fromforaff,
  importsfromforeignparentgroup as in_imp_fromfpg, 
  income as in_income,
  profit as in_profit, 
  sales as in_sales,
  salesgoods as in_salesgoods,
  salesservices as in_salesserv,
  salesservicestoforeignpersons as in_salesservtoforpersons,
  salesservicestouspersons as in_salesservtouspersons,
  valueadded as in_valueadded

  %if &year lt 2012 and &year ge 2007 %then %do; 
    ,
    CE as in_impfurthproc_fromforaff,
    CG as in_impfurthproc_fromfpg,
    CI as in_impfurthproc_fromothfor
  %end;
  %if &year ge 2007 %then %do;
    ,
    importsfromunaffiliatedforeigner as in_imp_fromothforr, 
    importsofgoodsforfurtherprocessi as in_impfurthproc,
    importsofgoodsforotheruses as in_impothuses,
    importsofgoodsforotherusesfromfo as in_impothuses_fromforaff,
    importsofgoodsforotherusesfromfp as in_impothuses_fromfpg,
    importsofgoodsforotherusesfromun as in_impothuses_fromothfor, 
    importsofgoodsforresale as in_impresale,
    importsofgoodsforresalefromforei as in_impresale_fromforaff,
    importsofgoodsforresalefromfpg as in_impresale_fromfpg,
    importsofgoodsforresalefromunaff as in_impresale_fromothfor
  %end; 



  from formnl;
  
  


proc export data = output.formnl_us_affiliates&year
    file = "&output/formnl_us_affiliates&year..dta"
    dbms=stata replace;
run;


%mend;



%macro loop_years;
  %do yr= 2007 %to 2007;
    %pull_formnl(&yr.);
  %end;

%mend;

%loop_years;

